package com.example.mysqladmin.service;

import com.example.mysqladmin.util.SqlUtils;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@RequiredArgsConstructor
public class DatabaseService {
    
    private final JdbcTemplate jdbcTemplate;

    public List<String> getAllDatabases() {
        String sql = "SHOW DATABASES";
        try {
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取数据库列表失败: " + e.getMessage());
        }
    }

    public List<String> getAllTables(String database) {
        String sql = String.format("SHOW TABLES FROM `%s`", database);
        try {
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取表列表失败: " + e.getMessage());
        }
    }

    public List<Map<String, Object>> getTableStructure(String database, String table) {
        String sql = "SELECT \n" +
                "                COLUMN_NAME as name,\n" +
                "                DATA_TYPE as type,\n" +
                "                IS_NULLABLE as nullable,\n" +
                "                COLUMN_KEY as keyType,\n" +
                "                EXTRA as extra,\n" +
                "                COLUMN_COMMENT as comment\n" +
                "            FROM information_schema.COLUMNS \n" +
                "            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?\n" +
                "            ORDER BY ORDINAL_POSITION";
        
        return jdbcTemplate.queryForList(sql, database, table);
    }

    public List<Map<String, Object>> executeQuery(String sql) {
        sql = SqlUtils.sanitizeSql(sql);
        if (!SqlUtils.isSelectQuery(sql)) {
            throw new IllegalArgumentException("只允许执行SELECT查询");
        }
        return jdbcTemplate.queryForList(sql);
    }

    public int executeUpdate(String sql) {
        sql = SqlUtils.sanitizeSql(sql);
        return jdbcTemplate.update(sql);
    }

    public List<Map<String, Object>> getTableData(String database, String table, int page, int size) {
        try {
            String sql = String.format("SELECT * FROM `%s`.`%s` LIMIT %d OFFSET %d", 
                database, table, size, (page - 1) * size);
            System.out.println("Executing SQL: " + sql);
            
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            System.out.println("Query result size: " + result.size());
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取表数据失败: " + e.getMessage());
        }
    }

    public int getTableTotal(String database, String table) {
        String sql = String.format("SELECT COUNT(*) FROM `%s`.`%s`", database, table);
        try {
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取表记录数失败: " + e.getMessage());
        }
    }

    public List<String> getTableColumns(String database, String table) {
        String sql = String.format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s' ORDER BY ORDINAL_POSITION", 
                database, table);
        return jdbcTemplate.queryForList(sql, String.class);
    }

    public List<Map<String, Object>> getAllTableData(String database, String table) {
        String sql = String.format("SELECT * FROM `%s`.`%s`", database, table);
        return jdbcTemplate.queryForList(sql);
    }

    public int insertRow(String database, String table, Map<String, Object> data) {
        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("插入数据不能为空");
        }

        try {
            // 构建插入SQL
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO `").append(database).append("`.`").append(table).append("` (");
            
            // 添加字段名
            String columns = String.join("`, `", data.keySet());
            sql.append("`").append(columns).append("`) VALUES (");
            
            // 添加参数占位符
            String placeholders = String.join(", ", Collections.nCopies(data.size(), "?"));
            sql.append(placeholders).append(")");
            
            String sqlString = sql.toString();
            System.out.println("Executing SQL: " + sqlString);
            System.out.println("Parameters: " + data.values());
            
            // 执行插入
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sqlString, Statement.RETURN_GENERATED_KEYS);
                int i = 1;
                for (Object value : data.values()) {
                    ps.setObject(i++, value);
                }
                return ps;
            }, keyHolder);
            
            // 返回新插入记录的ID
            return keyHolder.getKey().intValue();
            
        } catch (Exception e) {
            System.err.println("Insert failed: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("插入数据失败: " + e.getMessage());
        }
    }

    public void updateRow(String database, String table, String id, Map<String, Object> data) {
        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("更新数据不能为空");
        }

        try {
            // 构建更新SQL
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE `").append(database).append("`.`").append(table).append("` SET ");
            
            List<Object> params = new ArrayList<>();
            
            // 添加更新字段
            int i = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (i > 0) {
                    sql.append(", ");
                }
                sql.append("`").append(entry.getKey()).append("` = ?");
                params.add(entry.getValue());
                i++;
            }
            
            // 添加WHERE条件
            sql.append(" WHERE id = ?");
            params.add(id);
            
            String sqlString = sql.toString();
            System.out.println("Executing SQL: " + sqlString);
            System.out.println("Parameters: " + params);
            
            // 执行更新
            int updated = jdbcTemplate.update(sqlString, params.toArray());
            
            if (updated == 0) {
                throw new RuntimeException("未找到要更新的记录");
            } else if (updated > 1) {
                throw new RuntimeException("更新异常：影响了多条记录");
            }
            
            System.out.println("Update successful, rows affected: " + updated);
            
        } catch (Exception e) {
            System.err.println("Update failed: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("更新数据失败: " + e.getMessage());
        }
    }

    public void deleteRow(String database, String table, Object id) {
        String sql = String.format("DELETE FROM `%s`.`%s` WHERE id = ?", database, table);
        jdbcTemplate.update(sql, id);
    }

    public Map<String, Object> getRow(String database, String table, String id) {
        String sql = String.format("SELECT * FROM `%s`.`%s` WHERE id = ?", database, table);
        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, id);
        if (results.isEmpty()) {
            throw new RuntimeException("未找到数据");
        }
        return results.get(0);
    }

    public Map<String, Object> getTableDataWithTotal(String database, String table, int page, int size) {
        Map<String, Object> result = new HashMap<>();
        try {
            // 先获取总记录数
            String countSql = String.format("SELECT COUNT(*) FROM `%s`.`%s`", database, table);
            Integer total = jdbcTemplate.queryForObject(countSql, Integer.class);
            
            // 获取分页数据
            String dataSql = String.format("SELECT * FROM `%s`.`%s` LIMIT %d OFFSET %d", 
                database, table, size, (page - 1) * size);
            
            System.out.println("Count SQL: " + countSql);
            System.out.println("Data SQL: " + dataSql);
            System.out.println("Total records: " + total);
            
            List<Map<String, Object>> data = jdbcTemplate.queryForList(dataSql);
            
            // 返回结果
            result.put("data", data);
            result.put("total", total);  // 返回总记录数，而不是当前页的记录数
            result.put("page", page);
            result.put("size", size);
            
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取表数据失败: " + e.getMessage());
        }
    }
} 